Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop
Imports System.Data.Entity
Imports MigraDoc.DocumentObjectModel
Imports MigraDoc.Rendering

Public Class <Database>DBContext
	Inherits DbContext
	Public Sub New()
		MyBase.New("<Database>DBContext")
		Entity.Database.SetInitializer(Of <Database>DBContext)(Nothing)
		Configuration.ProxyCreationEnabled = False
	End Sub

    <RTable Loop>
	Public Property <OwnerU>_<TableU>() As DbSet(Of <OwnerU>_<TableU>)

    </RTable Loop>
End Class

Public Class <Database>DataClass

    Public Sub ExportToExcel(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGridView)
        Try
            If (Grid.ColumnCount > 0) Then
                Dim oExcel As Excel.Application
                Dim oBook As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                oExcel = CType(CreateObject("Excel.Application"), Excel.Application)
                oExcel.Visible = False
                oBook = oExcel.Workbooks.Add
                oSheet = CType(oBook.Worksheets(1), Excel.Worksheet)

                oSheet.Range("B2").Value = Head
                oSheet.Range("B2").Font.Bold = True

                If Type = "One" Then

                    For s As Integer = 0 To Grid.ColumnCount - 1
                        oSheet.Range("B" & 4 + s).Value = Grid.Columns(s).HeaderText
                        oSheet.Range("C" & 4 + s).Value = Grid.SelectedRows.Item(0).Cells.Item(s).Value
                    Next

                    oSheet.Columns.AutoFit()

                ElseIf Type = "Many" Then

                    Dim DataArrayHead(0, 0 To Grid.ColumnCount - 1) As Object
                    For s As Integer = 0 To Grid.ColumnCount - 1
                        DataArrayHead(0, s) = Grid.Columns(s).HeaderText
                    Next

                    oSheet.Range("B4").Resize(1, Grid.ColumnCount).Value = DataArrayHead
                    oSheet.Range("B4").Resize(1, Grid.ColumnCount).Font.Bold = True

                    Dim DataArray(0 To Grid.RowCount - 1, 0 To Grid.ColumnCount - 1) As Object
                    For r As Integer = 0 To Grid.RowCount - 1
                        For s As Integer = 0 To Grid.ColumnCount - 1
                            DataArray(r, s) = Grid.Rows.Item(r).Cells.Item(s).Value
                        Next
                    Next

                    oSheet.Range("B5").Resize(Grid.RowCount, Grid.ColumnCount).Value = DataArray
                    oSheet.Columns.AutoFit()

                End If

                oExcel.Visible = True
                oExcel = Nothing
                oSheet = Nothing
            End If
        Catch
        End Try
    End Sub

    Public Sub ExportToWord(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGridView)
        Try
            If (Grid.ColumnCount > 0) Then
                Dim oWord As Word.Application
                Dim oDoc As Word.Document
                Dim oTable As Word.Table
                Dim oPara1 As Word.Paragraph

                oWord = CType(CreateObject("Word.Application"), Word.Application)
                oWord.Visible = False
                oDoc = oWord.Documents.Add
                With oDoc.PageSetup
                    .PaperSize = Word.WdPaperSize.wdPaperA4
                    .Orientation = Word.WdOrientation.wdOrientLandscape
                    .LeftMargin = oWord.CentimetersToPoints(1)
                    .BottomMargin = oWord.CentimetersToPoints(1)
                    .TopMargin = oWord.CentimetersToPoints(1)
                End With

                oPara1 = oDoc.Content.Paragraphs.Add
                oPara1.Range.Text = Head
                oPara1.Range.Font.Bold = CInt(False)
                oPara1.Range.Font.Size = 8
                oPara1.Format.SpaceAfter = 8
                oPara1.Range.InsertParagraphAfter()

                If Type = "One" Then

                    oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, Grid.ColumnCount, 2)
                    oTable.Range.ParagraphFormat.SpaceAfter = 2

                    For i As Integer = 0 To Grid.ColumnCount - 1
                        oTable.Cell(i + 1, 1).Range.InsertAfter(Grid.Columns(i).HeaderText)
                        oTable.Cell(i + 1, 2).Range.Text = System.Convert.ToString(IIf(Grid.SelectedRows.Item(0).Cells.Item(i).Value Is System.DBNull.Value, "", Grid.SelectedRows.Item(0).Cells.Item(i).Value))
                    Next

                    oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
                    oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble

                    oTable.AllowAutoFit = True
                    oTable.Columns.AutoFit()

                ElseIf Type = "Many" Then

                    oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, Grid.RowCount + 1, Grid.ColumnCount)
                    oTable.Range.ParagraphFormat.SpaceAfter = 2

                    For i As Integer = 0 To Grid.ColumnCount - 1
                        oTable.Cell(1, i + 1).Range.InsertAfter(Grid.Columns(i).HeaderText)
                    Next

                    For r As Integer = 0 To Grid.RowCount - 1
                        For s As Integer = 0 To Grid.ColumnCount - 1
                            oTable.Cell(r + 2, s + 1).Range.Text = System.Convert.ToString(IIf(Grid.Rows.Item(r).Cells.Item(s).Value Is System.DBNull.Value, "", Grid.Rows.Item(r).Cells.Item(s).Value))
                        Next
                    Next

                    oTable.Rows.Item(1).Cells.Shading.BackgroundPatternColorIndex = Word.WdColorIndex.wdGray25

                    oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
                    oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble

                    oTable.AllowAutoFit = True
                    oTable.Columns.AutoFit()

                End If

                oWord.Visible = True
                oWord.NormalTemplate.Saved = True
                oWord = Nothing
                oDoc = Nothing
            End If
        Catch
        End Try
    End Sub

    'Public Sub ExportToPdf(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGridView)
    '    Try
    '        If (Grid.ColumnCount > 0) Then
    '            Dim oWord As Word.Application
    '            Dim oDoc As Word.Document
    '            Dim oTable As Word.Table
    '            Dim oPara1 As Word.Paragraph

    '            oWord = CType(CreateObject("Word.Application"), Word.Application)
    '            oWord.Visible = False
    '            oDoc = oWord.Documents.Add
    '            With oDoc.PageSetup
    '                .PaperSize = Word.WdPaperSize.wdPaperA4
    '                .Orientation = Word.WdOrientation.wdOrientLandscape
    '                .LeftMargin = oWord.CentimetersToPoints(1)
    '                .BottomMargin = oWord.CentimetersToPoints(1)
    '                .TopMargin = oWord.CentimetersToPoints(1)
    '            End With

    '            oPara1 = oDoc.Content.Paragraphs.Add
    '            oPara1.Range.Text = Head
    '            oPara1.Range.Font.Bold = CInt(False)
    '            oPara1.Range.Font.Size = 8
    '            oPara1.Format.SpaceAfter = 8
    '            oPara1.Range.InsertParagraphAfter()

    '            If Type = "One" Then

    '                oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, Grid.ColumnCount, 2)
    '                oTable.Range.ParagraphFormat.SpaceAfter = 2

    '                For i As Integer = 0 To Grid.ColumnCount - 1
    '                    oTable.Cell(i + 1, 1).Range.InsertAfter(Grid.Columns(i).HeaderText)
    '                    oTable.Cell(i + 1, 2).Range.Text = System.Convert.ToString(IIf(Grid.SelectedRows.Item(0).Cells.Item(i).Value Is System.DBNull.Value, "", Grid.SelectedRows.Item(0).Cells.Item(i).Value))
    '                Next

    '                oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
    '                oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble

    '                oTable.AllowAutoFit = True
    '                oTable.Columns.AutoFit()

    '            ElseIf Type = "Many" Then

    '                oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, Grid.RowCount + 1, Grid.ColumnCount)
    '                oTable.Range.ParagraphFormat.SpaceAfter = 2

    '                For i As Integer = 0 To Grid.ColumnCount - 1
    '                    oTable.Cell(1, i + 1).Range.InsertAfter(Grid.Columns(i).HeaderText)
    '                Next

    '                For r As Integer = 0 To Grid.RowCount - 1
    '                    For s As Integer = 0 To Grid.ColumnCount - 1
    '                        oTable.Cell(r + 2, s + 1).Range.Text = System.Convert.ToString(IIf(Grid.Rows.Item(r).Cells.Item(s).Value Is System.DBNull.Value, "", Grid.Rows.Item(r).Cells.Item(s).Value))
    '                    Next
    '                Next

    '                oTable.Rows.Item(1).Cells.Shading.BackgroundPatternColorIndex = Word.WdColorIndex.wdGray25

    '                oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
    '                oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble

    '                oTable.AllowAutoFit = True
    '                oTable.Columns.AutoFit()

    '            End If

    '            Dim FilePath As String = System.IO.Path.GetTempPath & "\" & Date.Now.Second.ToString & Date.Now.Millisecond.ToString & ".pdf"
    '            oDoc.ExportAsFixedFormat(FilePath, Word.WdExportFormat.wdExportFormatPDF, True)
    '            oDoc.Close(Word.WdSaveOptions.wdDoNotSaveChanges)
    '            oWord.Quit()
    '        End If
    '    Catch
    '    End Try
    'End Sub

    Public Sub ExportToPdf(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGridView, ByVal GridRow As DataGridViewRow)
        Try
            Dim dt As DataTable
            If Type = "One" Then
                dt = DataGridViewRow_To_DataTable(Grid, GridRow, Head)
            Else
                dt = DataGridView_To_DataTable(Grid, Head)
            End If
            Dim pdfForm As New PDFform(dt, Head, Type)

            Dim document As Document = pdfForm.CreateDocument()
            document.UseCmykColor = True
            Dim pdfRenderer As New PdfDocumentRenderer(True)
            pdfRenderer.Document = document
            pdfRenderer.RenderDocument()

            Dim FilePath As String = System.IO.Path.GetTempPath & "\" & Date.Now.Second.ToString & Date.Now.Millisecond.ToString & ".pdf"
            pdfRenderer.Save(FilePath)
            Process.Start(FilePath)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Console.ReadLine()
        End Try
    End Sub

    'Converts the DataGridView to DataTable
    Public Function DataGridView_To_DataTable(dgv As DataGridView, tblName As [String], Optional minRow As Integer = 0) As DataTable
        Dim dt As New DataTable(tblName)

        ' Header columns
        For Each column As DataGridViewColumn In dgv.Columns
            Dim dc As New DataColumn(column.Name.ToString())
            dt.Columns.Add(dc)
        Next

        ' Data cells
        For i As Integer = 0 To dgv.Rows.Count - 1
            Dim row As DataGridViewRow = dgv.Rows(i)
            Dim dr As DataRow = dt.NewRow()
            For j As Integer = 0 To dgv.Columns.Count - 1
                dr(j) = If((row.Cells(j).Value Is Nothing), "", row.Cells(j).Value.ToString())
            Next
            dt.Rows.Add(dr)
        Next

        ' Related to the bug arround min size when using ExcelLibrary for export
        For i As Integer = dgv.Rows.Count To minRow - 1
            Dim dr As DataRow = dt.NewRow()
            For j As Integer = 0 To dt.Columns.Count - 1
                dr(j) = "  "
            Next
            dt.Rows.Add(dr)
        Next
        Return dt
    End Function

    'Converts the DataGridViewRow to DataTable
    Public Function DataGridViewRow_To_DataTable(dgv As DataGridView, dgvr As DataGridViewRow, tblName As [String], Optional minRow As Integer = 0) As DataTable
        Dim dt As New DataTable(tblName)

        ' Header columns
        Dim dc1 As New DataColumn("Column")
        dt.Columns.Add(dc1)
        Dim dc2 As New DataColumn("Value")
        dt.Columns.Add(dc2)

        For Each column As DataGridViewColumn In dgv.Columns
            Dim dr As DataRow = dt.NewRow()
            dr.Item(0) = dgv.Columns(column.Index).Name.ToString()
            dr.Item(1) = dgvr.Cells(column.Index).Value
            dt.Rows.Add(dr)
        Next
        Return dt
    End Function

End Class

